* ---------------------------------------------------------------------------------------------------
* consolidate_insurance_holdings: Consolidates imports of insurer holdings data
* ---------------------------------------------------------------------------------------------------

set maxvar 120000
cap mkdir $holdings/insurance_partial

* --------------------------------------
* Append life insurer files
* --------------------------------------

di "Processing bonds"
clear
local counter = 1
local bonds_files : dir "$temp/insurance/life/bonds" files "*.dta"
foreach file in `bonds_files' {
    local counter = `counter' + 1
    if mod(`counter', 100) == 0 {
        di "Processing file `counter'"
    }
    qui append using $temp/insurance/life/bonds/`file'
}
save $holdings/insurance_partial/life_insurance_bonds, replace

di "Processing common equities"
clear
local counter = 1
local common_equities_files : dir "$temp/insurance/life/common_equities" files "*.dta"
foreach file in `common_equities_files' {
    local counter = `counter' + 1
    if mod(`counter', 100) == 0 {
        di "Processing file `counter'"
    }
    qui append using $temp/insurance/life/common_equities/`file'
}
save $holdings/insurance_partial/life_insurance_common_equities, replace


di "Processing preferred equities"
clear
local counter = 1
local preferred_equities_files : dir "$temp/insurance/life/preferred_equities" files "*.dta"
foreach file in `preferred_equities_files' {
    local counter = `counter' + 1
    if mod(`counter', 100) == 0 {
        di "Processing file `counter'"
    }
    qui append using $temp/insurance/life/preferred_equities/`file'
}
save $holdings/insurance_partial/life_insurance_preferred_equities, replace


di "Processing other"
clear
local counter = 1
local other_files : dir "$temp/insurance/life/other" files "*.dta"
foreach file in `other_files' {
    local counter = `counter' + 1
    if mod(`counter', 100) == 0 {
        di "Processing file `counter'"
    }
    qui append using $temp/insurance/life/other/`file'
}
save $holdings/insurance_partial/life_insurance_other, replace

clear
append using $holdings/insurance_partial/life_insurance_bonds
drop if missing(marketvalue_usd)
append using $holdings/insurance_partial/life_insurance_common_equities
drop if missing(marketvalue_usd)
append using $holdings/insurance_partial/life_insurance_preferred_equities
drop if missing(marketvalue_usd)
append using $holdings/insurance_partial/life_insurance_other
drop if missing(marketvalue_usd)
compress
save $holdings/insurance_partial/life_insurance_master, replace

* --------------------------------------
* Append health insurer files
* --------------------------------------

di "Processing bonds"
clear
local counter = 1
local bonds_files : dir "$temp/insurance/health/bonds" files "*.dta"
foreach file in `bonds_files' {
    local counter = `counter' + 1
    if mod(`counter', 100) == 0 {
        di "Processing file `counter'"
    }
    qui append using $temp/insurance/health/bonds/`file'
}
save $holdings/insurance_partial/health_insurance_bonds, replace


di "Processing common equities"
clear
local counter = 1
local common_equities_files : dir "$temp/insurance/health/common_equities" files "*.dta"
foreach file in `common_equities_files' {
    local counter = `counter' + 1
    if mod(`counter', 100) == 0 {
        di "Processing file `counter'"
    }
    qui append using $temp/insurance/health/common_equities/`file'
}
save $holdings/insurance_partial/health_insurance_common_equities, replace


di "Processing preferred equities"
clear
local counter = 1
local preferred_equities_files : dir "$temp/insurance/health/preferred_equities" files "*.dta"
foreach file in `preferred_equities_files' {
    local counter = `counter' + 1
    if mod(`counter', 100) == 0 {
        di "Processing file `counter'"
    }
    qui append using $temp/insurance/health/preferred_equities/`file'
}
save $holdings/insurance_partial/health_insurance_preferred_equities, replace


di "Processing other"
clear
local counter = 1
local other_files : dir "$temp/insurance/health/other" files "*.dta"
foreach file in `other_files' {
    local counter = `counter' + 1
    if mod(`counter', 100) == 0 {
        di "Processing file `counter'"
    }
    qui append using $temp/insurance/health/other/`file'
}
save $holdings/insurance_partial/health_insurance_other, replace

clear
append using $holdings/insurance_partial/health_insurance_bonds
drop if missing(marketvalue_usd)
append using $holdings/insurance_partial/health_insurance_common_equities
drop if missing(marketvalue_usd)
append using $holdings/insurance_partial/health_insurance_preferred_equities
drop if missing(marketvalue_usd)
append using $holdings/insurance_partial/health_insurance_other
drop if missing(marketvalue_usd)
qui compress
save $holdings/insurance_partial/health_insurance_master, replace

* --------------------------------------
* Append P&C insurer files
* --------------------------------------

di "Processing bonds"
clear
local counter = 1
local bonds_files : dir "$temp/insurance/pc/bonds" files "*.dta"
foreach file in `bonds_files' {
    local counter = `counter' + 1
    if mod(`counter', 100) == 0 {
        di "Processing file `counter'"
    }
    qui append using $temp/insurance/pc/bonds/`file'
}
save $holdings/insurance_partial/pc_insurance_bonds, replace


di "Processing common equities"
clear
local counter = 1
local common_equities_files : dir "$temp/insurance/pc/common_equities" files "*.dta"
foreach file in `common_equities_files' {
    local counter = `counter' + 1
    if mod(`counter', 100) == 0 {
        di "Processing file `counter'"
    }
    qui append using $temp/insurance/pc/common_equities/`file'
}
save $holdings/insurance_partial/pc_insurance_common_equities, replace


di "Processing preferred equities"
clear
local counter = 1
local preferred_equities_files : dir "$temp/insurance/pc/preferred_equities" files "*.dta"
foreach file in `preferred_equities_files' {
    local counter = `counter' + 1
    if mod(`counter', 100) == 0 {
        di "Processing file `counter'"
    }
    qui append using $temp/insurance/pc/preferred_equities/`file'
}
save $holdings/insurance_partial/pc_insurance_preferred_equities, replace


di "Processing other"
clear
local counter = 1
local other_files : dir "$temp/insurance/pc/other" files "*.dta"
foreach file in `other_files' {
    local counter = `counter' + 1
    if mod(`counter', 100) == 0 {
        di "Processing file `counter'"
    }
    qui append using $temp/insurance/pc/other/`file'
}
save $holdings/insurance_partial/pc_insurance_other, replace

clear
append using $holdings/insurance_partial/pc_insurance_bonds
drop if missing(marketvalue_usd)
append using $holdings/insurance_partial/pc_insurance_common_equities
drop if missing(marketvalue_usd)
append using $holdings/insurance_partial/pc_insurance_preferred_equities
drop if missing(marketvalue_usd)
append using $holdings/insurance_partial/pc_insurance_other
drop if missing(marketvalue_usd)
compress
save $holdings/insurance_partial/pc_insurance_master, replace

* --------------------------------------
* Consolidate all files
* --------------------------------------

* Append all
clear
use $holdings/insurance_partial/life_insurance_master, clear
append using $holdings/insurance_partial/health_insurance_master
append using $holdings/insurance_partial/pc_insurance_master
format %tq date_q
compress
cap rename CUSIP cusip
foreach var of varlist * {
    local x = lower("`var'")
    cap rename `var' `x'
}
save "$insurance_holdings/all_insurance_master", replace

* ---------------------------------------------------------------------------------------------------
* Consolidate mortgage loans data
* ---------------------------------------------------------------------------------------------------

* Mortgage loans
clear
local files : dir "$temp/insurance/life/loans" files "*.dta"
foreach file in `files' {
    qui append using "$temp/insurance/life/loans/`file'"
}
local files : dir "$temp/insurance/health/loans" files "*.dta"
foreach file in `files' {
    qui append using "$temp/insurance/health/loans/`file'"
}
local files : dir "$temp/insurance/pc/loans" files "*.dta"
foreach file in `files' {
    qui append using "$temp/insurance/pc/loans/`file'"
}

drop if missing(Mortgage_Loan_Number) & missing(Asset_Type) & missing(Building_and_Land_Value) & missing(Change_in_Unrealized_Value) & missing(Book_Value_ex_Accrued_Interest)
compress
format %tq date_q

destring Building_and_Land_Value, force replace
cap drop building_land_value_usd
gen building_land_value_usd = Building_and_Land_Value * 1000

foreach var of varlist * {
    local x = lower("`var'")
    rename `var' `x'
}

save "$holdings/insurance_partial/insurance_mortgage_loans_all", replace
